
////////////////////////////////////////////////////////////////////////////////////////////
// 文件名: SysMenuDAL.gen.cs
//
// 模块名称: SysMenu
//
// 作者: zxf
//
// 历史记录（记录修改记录，修改文件后请添加修改记录，注明修改时间、修改人，修改内容）:
// 2013/1/21    zxf    创建文件
////////////////////////////////////////////////////////////////////////////////////////////

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Globalization;
using Model;
using Common;

namespace DAL
{
    /// <summary>
    /// SysMenu表数据访问层
    /// </summary>
    public class SysMenuDAL : DB
    {
        /// <summary>
        /// 根据Id字段获取SysMenuInfo实体对象
        /// </summary>
        /// <param name="id">Id</param>
        /// <returns>SysMenuInfo实体对象</returns>
        public static SysMenuInfo GetInfoById(string id)
        {
            //创建数据库对象
            HIDB.dbo con = HIDB.common.make();

            //添加输入输出参数
            IDataParameter[] sqlparams = { 
            new SqlParameter("@Id" ,id )
          };

            //执行命令返回DataTable对象
            DataTable dt = con.StoredDataTable("dbo.UP_SysMenu_Select", sqlparams);

            if (dt != null && dt.Rows.Count > 0)
            {
                var model = new SysMenuInfo
                {
                    Id = dt.Rows[0]["Id"].ToString(),
                    Name = dt.Rows[0]["Name"].ToString(),
                    ParentId = dt.Rows[0]["ParentId"].ToString(),
                    Url = dt.Rows[0]["Url"].ToString(),
                    Iconic = dt.Rows[0]["Iconic"].ToString(),
                    Sort = Int32.Parse(dt.Rows[0]["Sort"].ToString()),
                    Remark = dt.Rows[0]["Remark"].ToString(),
                    State = dt.Rows[0]["State"].ToString(),
                    CreatePerson = dt.Rows[0]["CreatePerson"].ToString(),
                    //CreateTime = DateTime.Parse(dt.Rows[0]["CreateTime"].ToString()),
                    //UpdateTime = DateTime.Parse(dt.Rows[0]["UpdateTime"].ToString()),
                    UpdatePerson = dt.Rows[0]["UpdatePerson"].ToString(),
                    IsLeaf = dt.Rows[0]["IsLeaf"].ToString(),
                    ParentName = dt.Rows[0][13].ToString()
                };
                return model;
            }

            return null;
        }

        /// <summary>
        /// 往SysMenu表内添加新记录
        /// </summary>
        /// <param>SysMenuInfo实体对象 <name>SysMenuInfo</name> </param>
        /// <param name="sysMenuInfo"> </param>
        /// <returns>添加记录索引值</returns>
        public static void Insert(SysMenuInfo sysMenuInfo)
        {
            //创建数据库对象
            HIDB.dbo con = HIDB.common.make();

            //添加输入输出参数
            IDataParameter[] sqlparams = { 
             new SqlParameter("@Id" ,sysMenuInfo.Id),
             new SqlParameter("@Name" ,sysMenuInfo.Name),
             new SqlParameter("@ParentId" ,sysMenuInfo.ParentId==null ? "" : sysMenuInfo.ParentId),
             new SqlParameter("@Url" ,sysMenuInfo.Url==null ? "" : sysMenuInfo.Url), 
             new SqlParameter("@Sort" ,sysMenuInfo.Sort.ToString(CultureInfo.InvariantCulture)), 
             new SqlParameter("@State" ,sysMenuInfo.State)};

            //返回新添加记录索引
            con.StoredUpdate("dbo.UP_SysMenu_Insert", sqlparams);
        }

        /// <summary>
        /// 更新SysMenu表记录
        /// </summary>
        /// <param>SysMenuInfo实体对象 <name>SysMenuInfo</name> </param>
        /// <param name="sysMenuInfo"> </param>
        public static void Update(SysMenuInfo sysMenuInfo)
        {
            //创建数据库对象
            HIDB.dbo con = HIDB.common.make();
            //添加输入输出参数
            IDataParameter[] sqlparams = { 
             new SqlParameter("@Id" ,sysMenuInfo.Id),
             new SqlParameter("@Name" ,sysMenuInfo.Name),
             new SqlParameter("@ParentId" ,sysMenuInfo.ParentId==null ? "" : sysMenuInfo.ParentId),
             new SqlParameter("@Url" ,sysMenuInfo.Url==null ? "" : sysMenuInfo.Url), 
             new SqlParameter("@Sort" ,sysMenuInfo.Sort.ToString(CultureInfo.InvariantCulture)), 
             new SqlParameter("@State" ,sysMenuInfo.State)};
            con.StoredUpdate("dbo.UP_SysMenu_Update", sqlparams);
        }

        /// <summary>
        /// 根据Id字段删除SysMenuInfo表信息
        /// </summary>
        /// <param name="id">Id</param>
        public static void Delete(string id)
        {
            //创建数据库对象
            HIDB.dbo con = HIDB.common.make();
            //添加输入输出参数
            IDataParameter[] sqlparams = { 
            new SqlParameter("@Id" ,id)
          };
            //执行操作
            con.StoredUpdate("dbo.UP_SysMenu_Delete", sqlparams);
        }

        /// <summary>
        /// 获取SysMenuInfo对象列表
        /// </summary>
        public static List<SysMenuInfo> GetInfoList()
        {
            //创建数据库对象
            HIDB.dbo con = HIDB.common.make();
            var list = new List<SysMenuInfo>();
            DataTable dt = con.GetDataTable("SELECT [Id],[Name],[ParentId],[Url],[Sort],[State]   FROM [dbo].[SysMenu] order by Sort");

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    var model = new SysMenuInfo
                    {
                        Id = row["Id"].ToString(),
                        Name = row["Name"].ToString(),
                        ParentId = row["ParentId"].ToString(),
                        Url = row["Url"].ToString(),
                        Sort = Int32.Parse(row["Sort"].ToString()),
                        State = row["State"].ToString()
                    };
                    list.Add(model);
                }
            }

            return list;
        }
        public static List<DBDeploy> GetDBList(string dbType)
        {
            //创建数据库对象
            HIDB.dbo con = HIDB.common.make();
            var list = new List<string>();
            DataTable dt = new DataTable();
            if (string.IsNullOrEmpty(dbType))
                dt = con.GetDataTable("select DBName,DisplayName,DBType,DBStatus,DBJobStatus from [Working_db].[DBDeploy] order by CreateTime");
            else
            {
                SqlParameter[] para = new SqlParameter[] { 
                new SqlParameter("@DBType",dbType)
                };
                dt = con.GetDataTable("select DBName,DisplayName,DBType,DBStatus,DBJobStatus from [Working_db].[DBDeploy] where DBType=@DBType order by CreateTime", para);
            }
            return dt.MapToList<DBDeploy>();
        }
        public List<DBDeploy> GetDBListInnerJob()
        {
            //创建数据库对象
            HIDB.dbo con = base.MSDB(); ; 
            DataTable dt = new DataTable();
            dt = con.GetDataTable(@"select distinct e.DBName,e.DisplayName,e.CreateTime
from sysjobs a 
inner join sysjobschedules c on a.job_id=c.job_id 
inner join MDM_Platform.Working_db.DBDeploy e on left(a.name,32)=e.DBName
order by e.CreateTime desc");
             
            return dt.MapToList<DBDeploy>();
        }
    }
}